using System;
using System.Collections.Generic;
using System.Text;
using Fieldbook.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using Fieldbook.Common;

namespace Fieldbook.DAL
{
    public static class FieldPlantSpecieDAO
    {
        #region select
        private static List<FieldPlantSpecie> LoadPlantSpecies(SqlDatabase db, DbCommand command)
        {
            List<FieldPlantSpecie> res = new List<FieldPlantSpecie>();
            IDataReader reader = null;
            try
            {
                reader = db.ExecuteReader(command);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        FieldPlantSpecie entity = new FieldPlantSpecie();
                        entity.FieldPlantSpecieID = reader.GetInt64(0);
                        entity.FieldID = reader.GetInt64(1);
                        entity.PlantSpecieID = reader.GetInt64(2);
                        entity.Sort = reader.GetString(3);
                        entity.SpecieStartDate = reader.GetDateTime(4);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<FieldPlantSpecie > LoadHouseHoldPlantSpecies(long userID)
        {
            string selectSQL = @"SELECT FPS.FIELDPLANTSPECIEID, FPS.FIELDID,  FPS.PLANTSPECIEID, PS.DESCRIPTION + ' (' + FPS.SORT + ' )' AS DESCRIPTION, FPS.SPECIESTARTDATE
FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN FIELD F ON F.OWNERID = HH1.MEMBERID
INNER JOIN FIELDPLANTSPECIE FPS ON FPS.FIELDID = F.FIELDID 
	AND FPS.SPECIESTARTDATE = (SELECT MAX(SPECIESTARTDATE) FROM FIELDPLANTSPECIE WHERE FIELDID = F.FIELDID)
INNER JOIN PLANTSPECIE PS ON PS.PLANTSPECIEID = FPS.PLANTSPECIEID
WHERE HH.MEMBERID = @MEMBERID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            return LoadPlantSpecies(db, cmd);
        }

        public static List<FieldPlantSpecie> LoadFieldPlantSpecies(long userID, long fieldID, string orderBy)
        {
            string selectSQL = String.Format(@"SELECT FPS.FIELDPLANTSPECIEID, FPS.FIELDID,  FPS.PLANTSPECIEID, PS.DESCRIPTION + ' (' + FPS.SORT + ' )' AS DESCRIPTION, FPS.SPECIESTARTDATE
FROM HOUSEHOLD HH 
INNER JOIN HOUSEHOLD HH1 ON HH.HEADID = HH1.HEADID
INNER JOIN FIELD F ON F.OWNERID = HH1.MEMBERID
INNER JOIN FIELDPLANTSPECIE FPS ON FPS.FIELDID = F.FIELDID 
INNER JOIN PLANTSPECIE PS ON PS.PLANTSPECIEID = FPS.PLANTSPECIEID
WHERE FPS.FIELDID = @FIELDID AND HH.MEMBERID = @MEMBERID {0}", orderBy);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@FIELDID", DbType.Int64, fieldID);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            return LoadPlantSpecies(db, cmd);
        }
        #endregion

        #region insert
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSQL = @"INSERT INTO dbo.FieldPlantSpecie (FIELDID,PLANTSPECIEID,SORT,SPECIESTARTDATE) 
                    VALUES (@FIELDID,@PLANTSPECIEID,@SORT,@SPECIESTARTDATE) SET @FIELDPLANTSPECIEID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@FIELDPLANTSPECIEID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@FIELDID", DbType.Int64);
            db.AddInParameter(insertCommand, "@PLANTSPECIEID", DbType.Int64);
            db.AddInParameter(insertCommand, "@SORT", DbType.String);
            db.AddInParameter(insertCommand, "@SPECIESTARTDATE", DbType.DateTime);
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, FieldPlantSpecie entity)
        {
            db.SetParameterValue(cmd, "@FIELDID", entity.FieldID);
            db.SetParameterValue(cmd, "@PLANTSPECIEID", entity.PlantSpecieID);
            db.SetParameterValue(cmd, "@SORT", entity.Sort);
            db.SetParameterValue(cmd, "@SPECIESTARTDATE", entity.SpecieStartDate);
        }
        public static bool InsertFieldPlantSpecie(FieldPlantSpecie entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.FieldPlantSpecieID = (long?)db.GetParameterValue(cmd, "@FIELDPLANTSPECIEID");
            }
            return res;
        }
        #endregion
    }
}
